﻿using Dapper;
using Domain.Entity;
using Domain.Entity.zct;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class RoleRepository : IRoleRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        //查全部
        public async Task<List<Role>> GetAll()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select role_id,role_code,role_name,role_status,role_sort,remark from [dbo].[zct_role] where is_delete=0";
                return sqlConnection.Query<Role>(sql).ToList();
            }
        }

        //ID查
        public async Task<Role> GetID(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select role_id,role_code,role_name,role_status,role_sort,remark from [dbo].[zct_role] where is_delete=0 AND Role_id=@id";
                var parameters = new DynamicParameters();
                parameters.Add("@id", id);
                return await sqlConnection.QueryFirstOrDefaultAsync<Role>(sql, parameters);
            }
        }

        //分页查全部
        public async Task<FenYe<Role>> GetAll(FenYe<Role> fenYe, string? Role_code, string? Role_name, int? Role_status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                string whereBuilder = $" is_delete=0 ";
                var parameters = new DynamicParameters();

                if (!string.IsNullOrEmpty(Role_code))
                {
                    whereBuilder += $" and( Role_code like '%{Role_code}%' or Role_name like '%{Role_name}%') ";
                }

                if (Role_status.HasValue)
                {
                    whereBuilder += $" and Role_status = {Role_status}";
                }

                // 分页参数
                parameters.Add("@pageSize", 10);
                parameters.Add("@keyName", "role_sort"); // 主键字段
                parameters.Add("@tabelName", "zct_role");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder);
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<Role>(sql, parameters);

                // 设置分页结果
                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        //停用
        public async Task<int> GetDeactivated(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[zct_role] set Role_status=1 where Role_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //删除
        public async Task<int> GetDelete(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var checkExistsSql = "SELECT COUNT(1) FROM zct_role WHERE role_id = @RoleId AND is_delete = 0";

                var exists = await sqlConnection.QuerySingleAsync<int>(checkExistsSql, new { RoleId = id });
                if (exists == 0)
                {
                    return -1;
                }

                var checkUserAssignedSql = @"
                SELECT COUNT(*) 
                FROM zct_user u
                INNER JOIN zct_user_role ur ON u.user_id = ur.user_id 
                WHERE ur.role_id = @RoleId AND u.is_delete = 0";

                var userAssignedCount = await sqlConnection.QuerySingleAsync<int>(checkUserAssignedSql, new { RoleId = id });

                if (userAssignedCount > 0)
                {
                    return 0;
                }

                var updateSql = @"
                UPDATE zct_role
                SET is_delete = 1
                    -- , update_time = GETDATE() -- 根据数据库类型调整 (SQL Server: GETDATE(), MySQL: NOW())
                WHERE role_id = @RoleId AND is_delete = 0";

                var rowsAffected = await sqlConnection.ExecuteAsync(updateSql, new { RoleId = id });
                return rowsAffected > 0 ? 1 : -1;
            }
        }

        //启用
        public async Task<int> GetEnable(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[zct_role] set Role_status=0 where Role_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //添加
        public async Task<int> GetAdd(Role role, int[] permissionIds)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                using (var transaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        // 1. 插入角色数据并获取生成的Role_id
                        var roleSql = @"
                    INSERT INTO zct_role (
                        role_code,
                        role_name,
                        role_status,
                        role_sort,
                        is_delete,
                        remark
                    ) 
                    OUTPUT INSERTED.role_id
                    VALUES (
                        @Role_code,
                        @Role_name,
                        @Role_status,
                        @Role_sort,
                        @Is_delete,
                        @Remark
                    );";

                        var roleParams = new DynamicParameters();
                        roleParams.Add("@Role_code", role.Role_code);
                        roleParams.Add("@Role_name", role.Role_name);
                        roleParams.Add("@Role_status", role.Role_status);
                        roleParams.Add("@Role_sort", role.Role_sort);
                        roleParams.Add("@Is_delete", role.Is_delete);
                        roleParams.Add("@Remark", role.Remark ?? (object)DBNull.Value);

                        // 执行插入并获取新角色的ID
                        int roleId = await sqlConnection.ExecuteScalarAsync<int>(
                            roleSql,
                            roleParams,
                            transaction: transaction
                        );

                        // 2. 插入角色权限关联数据
                        if (permissionIds != null && permissionIds.Length > 0)
                        {
                            var permissionSql = @"
                        INSERT INTO zct_role_permission (
                            role_id,
                            permission_id
                        ) VALUES (
                            @RoleId,
                            @PermissionId
                        )";

                            foreach (int permissionId in permissionIds)
                            {
                                await sqlConnection.ExecuteAsync(
                                    permissionSql,
                                    new { RoleId = roleId, PermissionId = permissionId },
                                    transaction: transaction
                                );
                            }
                        }

                        // 提交事务
                        transaction.Commit();
                        return roleId;
                    }
                    catch (Exception ex)
                    {
                        // 记录异常日志（可选）
                        Console.WriteLine($"操作失败：{ex.Message}");

                        // 回滚事务
                        transaction.Rollback();
                        throw; // 重新抛出异常
                    }
                }
            }
        }

        //编辑
        public async Task<int> GetEdit(Role role, int[] permissionIds)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                using (var transaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        // 1. 更新角色基本信息
                        var updateRoleSql = @"
                    UPDATE zct_role
                    SET 
                        role_name = @Role_name,
                        role_status = @Role_status,
                        role_sort = @Role_sort,
                        is_delete = @Is_delete,
                        remark = @Remark
                    WHERE role_id = @Role_id;";

                        var roleParams = new DynamicParameters();
                        roleParams.Add("@Role_id", role.Role_id);
                        roleParams.Add("@Role_name", role.Role_name);
                        roleParams.Add("@Role_status", role.Role_status);
                        roleParams.Add("@Role_sort", role.Role_sort);
                        roleParams.Add("@Is_delete", role.Is_delete);
                        roleParams.Add("@Remark", role.Remark ?? (object)DBNull.Value);

                        int rowsAffected = await sqlConnection.ExecuteAsync(
                            updateRoleSql,
                            roleParams,
                            transaction: transaction
                        );

                        // 2. 删除该角色原有的所有权限关联
                        var deletePermissionsSql = @"
                    DELETE FROM zct_role_permission 
                    WHERE role_id = @RoleId";

                        await sqlConnection.ExecuteAsync(
                            deletePermissionsSql,
                            new { RoleId = role.Role_id },
                            transaction: transaction
                        );

                        // 3. 插入新的权限关联
                        if (permissionIds != null && permissionIds.Length > 0)
                        {
                            var insertPermissionsSql = @"
                        INSERT INTO zct_role_permission (
                            role_id,
                            permission_id
                        ) VALUES (
                            @RoleId,
                            @PermissionId
                        )";

                            foreach (int permissionId in permissionIds)
                            {
                                await sqlConnection.ExecuteAsync(
                                    insertPermissionsSql,
                                    new { RoleId = role.Role_id, PermissionId = permissionId },
                                    transaction: transaction
                                );
                            }
                        }

                        // 提交事务
                        transaction.Commit();
                        return rowsAffected;
                    }
                    catch (Exception ex)
                    {
                        // 记录异常日志（可选）
                        Console.WriteLine($"操作失败：{ex.Message}");

                        // 回滚事务
                        transaction.Rollback();
                        throw; // 重新抛出异常
                    }
                }
            }
        }
    }
}